package belf.migrate.plugin.kingbase;

import belf.migrate.api.exception.DataTypeMappingNotExistException;
import belf.migrate.api.table.schema.*;
import belf.migrate.api.taskconf.TaskConf;
import belf.migrate.api.taskconf.typemapping.TypeMappingChecker;
import belf.migrate.api.util.CatalogUtils;
import belf.migrate.core.jdbc.DBFunctionMapping;
import belf.migrate.core.jdbc.DBTypeMapping;
import belf.migrate.core.jdbc.FunctionModel;
import belf.migrate.core.jdbc.TypeModel;
import belf.migrate.core.util.StringTool;
import org.apache.commons.lang3.StringUtils;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

import static belf.migrate.api.common.Preconditions.checkNotNull;

/**
 * 通过原表的字段元信息，构建目标表
 */
public class KingbaseCreateTableSqlBuilder {

    private final TablePath tablePath;
    private TaskConf taskConf = null;

    private final List<ColumnMetaData> columns;
    private final PrimaryKey primaryKey;
    private final List<ForeignKey> foreignKeys;
    private final List<IndexKey> indexKeys;
    private final List<UniqueKey> uniqueKeys;
    private final List<CheckConstraint> checkConstraints;


    private String fieldCase;

    private String taskType;
    /**
     * 字段映射关系
     */
    private final Map<String, TypeModel> mappings;
    /**
     * 函数映射关系
     */
    private final Map<String, FunctionModel> funcMappings;

    /**
     * 通过目标表卢健、原表元数据、任务类型创建构造方法
     * @param tablePath 目标表路径
     * @param sourceTableSchema 原表的元数据信息，含字段信息、主键、外键、索引
     * @param taskType 表示进行迁移任务的类型，比如“MYSQL-DAMENG”。为各种mapping.xml的root element的ID
     */
    private KingbaseCreateTableSqlBuilder(TablePath tablePath, TableSchema sourceTableSchema, String taskType, TaskConf taskConf) {
        checkNotNull(tablePath.getTableName(), "tableName must not be null");
        this.tablePath = tablePath;
        this.columns = sourceTableSchema.getColumns();
        this.primaryKey = sourceTableSchema.getPrimaryKey();
        this.foreignKeys = sourceTableSchema.getForeignKeys();
        this.indexKeys = sourceTableSchema.getIndexKeys();
        this.uniqueKeys = sourceTableSchema.getUniqueKeys();
        this.checkConstraints = sourceTableSchema.getCheckConstraints();
        this.mappings = DBTypeMapping.getInstance().getMapping(taskType);
        this.funcMappings = DBFunctionMapping.getInstance().getMapping(taskType);
        this.taskType = taskType;
        this.taskConf = taskConf;
    }

    public static KingbaseCreateTableSqlBuilder builder(TablePath tablePath,
                                                        TableSchema sourceTableSchema,
                                                        String taskType,
                                                        TaskConf taskConf) {
        return new KingbaseCreateTableSqlBuilder(tablePath, sourceTableSchema, taskType, taskConf);
    }

    public KingbaseCreateTableSqlBuilder fieldCase(String fieldCase) {
        this.fieldCase = fieldCase;
        return this;
    }

    public List<String> createIndexes() {
        List<String> indexeSqls = new ArrayList<>();
        String sqlTemplate = "CREATE %s INDEX %s ON %s (%s %s)";
        indexKeys.forEach(new Consumer<IndexKey>() {
            @Override
            public void accept(IndexKey indexKey) {
                //不用对主键再建立索引: mysql 对主键返回索引名是“PRIMARY”
                if (indexKey.getIndexName().equalsIgnoreCase("PRIMARY")){
                    return;
                }
                //不用对主键再建立索引: 主键字段完全包含索引字段
                if (String.join(",", primaryKey.getColumnNames()).toUpperCase()
                        .contains(String.join(",", indexKey.getColumnNames()).toUpperCase())){
                    return;
                }

                String PREFIX = "";
                String SUFFIX = "";
                if (indexKey.isUnique()){
                    PREFIX = "UNIQUE ";
                } /*else if (indexKey.getIndexTypeName() != null) {
                    if (indexKey.getIndexTypeName().equalsIgnoreCase("FULLTEXT")) {
                        PREFIX = "CONTEXT";
                        SUFFIX = " LEXER DEFAULT_LEXER";
                    } else if (indexKey.getIndexTypeName().equalsIgnoreCase("SPATIAL")) {
                        PREFIX = "SPATIAL";
                    }
                }*/
                String indexName = indexKey.getIndexName();
                String tableName = tablePath.getFullName();
                String columnNames = String.join(",", indexKey.getColumnNames()).toUpperCase();
                String asc = "";
                if (indexKey.getIndexOrder() == IndexKey.IndexOrder.ASC) {
                    asc = "ASC";
                } else if (indexKey.getIndexOrder() == IndexKey.IndexOrder.DESC) {
                    asc = "DESC";
                }

                // 达梦数据库里对于全文检索，要对每个字段单独索引。MySQL里是可以多个字段联合做FULLTEXT索引的
                if (indexKey.getIndexTypeName() != null
                        && indexKey.getIndexTypeName().equalsIgnoreCase("FULLTEXT")) {
                    int i = 1;
                    for (String columnName : indexKey.getColumnNames()) {
                        String indexSql = String.format(sqlTemplate, PREFIX, indexName + i++, tableName, columnName, asc);
                        if (indexSql.startsWith("CREATE CONTEXT")) {
                            indexSql += " LEXER DEFAULT_LEXER";
                        }
                        indexeSqls.add(indexSql);
                    }
                } else {
                    String newIndexName = "IDX_" + tablePath.getTableName() + "_"
                            + String.join("_", indexKey.getColumnNames());
                    String indexSql = String.format(sqlTemplate, PREFIX, newIndexName, tableName, columnNames, asc);
                    indexeSqls.add(indexSql);
                }
            }
        });
        return indexeSqls;
    }

    public List<String> createForeigns() {
        List<String> foreignSqls = new ArrayList<>();
        foreignKeys.forEach(new Consumer<ForeignKey>() {
            @Override
            public void accept(ForeignKey foreignKey) {
                StringBuilder sb = new StringBuilder();
                sb.append("ALTER TABLE ");
                sb.append(tablePath.getFullName());
                sb.append(" ADD CONSTRAINT ");
                //直接用原有的外键名，可能与索引名冲突
//                sb.append(foreignKey.getFkName().toUpperCase());
                sb.append("FK_" + tablePath.getTableName() + "_" + foreignKey.getPkTable() + "_" + foreignKey.getFkColumnName());
                sb.append(" FOREIGN KEY (");
                sb.append(foreignKey.getFkColumnName());
                sb.append(") REFERENCES ");
                if (foreignKey.getPkCatalog() != null) {
                    sb.append(foreignKey.getPkCatalog());
                    sb.append(".");
                } else if (foreignKey.getPkSchema() != null) {
                    sb.append(foreignKey.getPkSchema());
                    sb.append(".");
                }
                sb.append(foreignKey.getPkTable());
                sb.append("(");
                sb.append(foreignKey.getPkColumnName());
                sb.append(")");
                if (foreignKey.getDeleteRule() != null
                        && (foreignKey.getDeleteRule().equals(ForeignKey.ConstraintAction.CASCADE)
                        || foreignKey.getDeleteRule().equals(ForeignKey.ConstraintAction.RESTRICT))) {
                    sb.append(" ON DELETE " + foreignKey.getDeleteRule().getExpression());
                }
                if (foreignKey.getUpdateRule() != null
                        && (foreignKey.getUpdateRule().equals(ForeignKey.ConstraintAction.CASCADE)
                        || foreignKey.getUpdateRule().equals(ForeignKey.ConstraintAction.RESTRICT))) {
                    sb.append(" ON UPDATE " + foreignKey.getDeleteRule().getExpression());
                }
                sb.append(";");

                foreignSqls.add(sb.toString());
            }
        });
        return foreignSqls;
    }

    public String createTable() throws DataTypeMappingNotExistException {
        List<String> sqls = new ArrayList<>();
        sqls.add(
                String.format(
                        "CREATE TABLE %s (\n%s",
                        tablePath.getSchemaAndTableName(),
                        buildColumnsIdentifySql()).trim());
        if (primaryKey != null && !primaryKey.getColumnNames().isEmpty()) {
            sqls.add(",\n\t" + buildPrimaryKeySql());
        }

        if (uniqueKeys != null && !uniqueKeys.isEmpty()) {
            sqls.add(",\n\t" + buildUniqueKeySql());
        }

        if (checkConstraints != null && !checkConstraints.isEmpty()) {
            sqls.add(",\n\t" + buildCheckConstraintSql());
        }
        return String.join(" ", sqls) + "\n);";
    }

    private String buildColumnsIdentifySql() throws DataTypeMappingNotExistException {
        List<String> columnSqls = new ArrayList<>();
        for (ColumnMetaData column : columns) {
            columnSqls.add("\t" + buildColumnIdentifySql(column));
        }

        return String.join(", \n", columnSqls);
    }

    private String buildColumnIdentifySql(ColumnMetaData column) throws DataTypeMappingNotExistException {
        final List<String> columnSqls = new ArrayList<>();
        columnSqls.add(CatalogUtils.getFieldCase(column.getColumnName(), fieldCase)); //字段名称

        String[] typenames = column.getColumnTypeName().split("\\s+");
        String typename = typenames[0].toUpperCase();
        String targetColumnType = "";

        // 先检查表级字段类型映射，确定目标端的字段类型
        targetColumnType = TypeMappingChecker.tableMapping(taskConf, column);
        // 再检查库级的数值类型映射，确定目标端的字段类型
        if (StringUtils.isEmpty(targetColumnType)) {
            targetColumnType = TypeMappingChecker.databaseMapping(taskConf, column);
        }

        // FIXME: 如果预定义模板漏掉了一种源端数据类型，这里可能出错，但是没有报异常，需要后面修复
        if (StringUtils.isEmpty(targetColumnType) && mappings.containsKey(typename)) {
            // 没有定义表级字段类型映射和库级数值类型映射，直接用模板中预定义的类型映射来确定目标端的字段类型
            if (mappings.containsKey(typename)) {
                if (StringUtils.isEmpty(targetColumnType)) {
                    targetColumnType = mappings.get(typename).getTo();
                }
                columnSqls.add(checkColumnLength(column, targetColumnType));  //字段数据类型
            } else {
                throw new DataTypeMappingNotExistException(taskConf.getTaskType(), typename);
            }
        }

        //TODO： 金仓不支持UNSIGNED关键字，后续再完善

        //处理自增字段
        if (column.isAutoIncrement()) {
            columnSqls.add("AUTO_INCREMENT");
        }

        // 字段值是否允许为空
        if (column.getNullable() == 0) {
            columnSqls.add("NOT NULL");
        }

        // 字段默认值
        if (column.getExtra() != null) {
            String defaultAttribute = null;
            ColumnExtra extra = column.getExtra();
            String defaultValue = StringTool.trimParentheses(extra.getDefaultValue());
            String sourceFuncName = StringTool.getFunctionName(defaultValue);
            // 对Source端函数进行替换，如果找到可替换函数，再添加DEFAULT值
            if (funcMappings != null
                    && StringUtils.isNotEmpty(sourceFuncName)
                    && funcMappings.containsKey(sourceFuncName.toUpperCase())) {
                String sinkFuncName = funcMappings.get(sourceFuncName.toUpperCase()).getTo();
                if (StringUtils.isNotEmpty(sinkFuncName)) {
                    defaultAttribute = " DEFAULT " + sinkFuncName + defaultValue.substring(sourceFuncName.length());
                }
            } else if (CatalogUtils.isNumeric(column.getColumnType())) { //再处理其他基本字段的默认值
                if (extra.getDefaultValue() != null) {
                    defaultAttribute = "DEFAULT " + defaultValue;
                }
            } else if (CatalogUtils.isString(column.getColumnType())) { //再处理其他基本字段的默认值
                if (extra.getDefaultValue() != null) {
                    defaultAttribute = "DEFAULT '" + defaultValue + "'";
                }
            } else {
                if (extra.getDefaultValue() != null) {
                    defaultAttribute = " DEFAULT " + extra.getDefaultValue();
                }
            }

            if (defaultAttribute != null) {
                // 处理日期类型字段在行进行UPDATE和DELETE时的联动动作
                // 注意源库如果是MySQL, 金仓要设置为MySQL兼容模式，否则"ON UPDATE/DELETE CURRENT_TIMESTAMP"无法通过
                if (extra.getExtra() != null && extra.getExtra().toUpperCase().startsWith("ON")) {
                    defaultAttribute += " " + extra.getExtra();
                }
                columnSqls.add(defaultAttribute);
            }
        }


        return String.join(" ", columnSqls);
    }

    /**
     * 金仓各种数值类型精度和标度的定义，参考：
     * <a href="https://bbs.kingbase.com.cn/kingbase-doc/v8/development/sql-plsql/sql-quick/datatype.html">KingbaseES SQL 数据类型</a>
     * @param column
     * @param targetColumnType
     * @return
     */
    private String checkColumnLength(ColumnMetaData column, String targetColumnType) {
        String retValue = targetColumnType;
        int columnType = column.getColumnType();

        // 时间类型单独处理精度，各种数据库一般有单独的表存放Column定义，这个表里有单独字段存放时间字段的精度
        if (columnType == Types.TIMESTAMP ||
                columnType == Types.TIMESTAMP_WITH_TIMEZONE ||
                columnType == Types.TIME) {
            if (column.getExtra() != null && column.getExtra().getDateTimePrecision() >= 0) {
                retValue += "(" + column.getExtra().getDateTimePrecision() + ")";
            }
        }

        // 已经指定了有明确长度的数值类型，后面不能再加(length)
        switch(targetColumnType.toUpperCase()) {
            case "NUMERIC": //以下类型可能加精度和标度
            case "NUMBER":
            case "DECIMAL":
                if (column.getScale() > 0) {
                    retValue += "(" + column.getPrecision() + "," + column.getScale() + ")";
                } else if (column.getPrecision() > 0) {
                    retValue += "(" + column.getPrecision()  + ")";
                }
                return retValue;
            case "FLOAT":  //以下类型可能加精度
            case "DOUBLE":
            case "CHAR":
            case "CHARACTER":
            case "VARCHAR":
            case "VARCHAR2":
            case "NVARCHAR":
            case "NVARCHAR2":
            case "DOUBLE PRECISION":
            case "RAW":
            case "BIT":
            case "BIT VAYRING":
            case "BINARY":
                if (column.getPrecision() > 0) {
                    retValue += "(" + column.getPrecision()  + ")";
                }
                return retValue;
            default: //其他类型没有精度和标度
                return targetColumnType;
        }
    }

    private String buildPrimaryKeySql() {
        String columnList = String.join(",", primaryKey.getColumnNames());
        String pkName = "pk_" + String.join("_", primaryKey.getColumnNames());
        return String.format("CONSTRAINT %s PRIMARY KEY (%s)", pkName, CatalogUtils.quoteIdentifier(columnList, fieldCase));
    }

    private String buildUniqueKeySql() {
        List<String> sqls = new ArrayList<>();
        for (UniqueKey uniqueKey : uniqueKeys) {
            String columnNames = String.join(",", uniqueKey.getColumns());
            String uniqueName = uniqueKey.getUniqueName();
            String sql = String.format("CONSTRAINT %s UNIQUE(%s)", uniqueName, columnNames);
            sqls.add(sql);
        }
        return String.join(",\n\t", sqls);
    }

    private String buildCheckConstraintSql() {
        List<String> sqls = new ArrayList<>();
        for (CheckConstraint checkConstraint : checkConstraints) {
            String condition = StringTool.trimParentheses(checkConstraint.getCondition());
            condition = condition.replace('`', '\"');
            condition = StringTool.removeChars(condition, "[]");
            String sql = String.format("CONSTRAINT \"%s\" CHECK (%s)",
                    checkConstraint.getConstraintName(),
                    condition);
            sqls.add(sql);
        }
        return String.join(",\n\t", sqls);
    }
}
